package com.mathofmattix.books;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class Catalog {

	private Connection catalogConnection = null;
	private static final String WRITE_OBJECT_SQL = "INSERT INTO bookCatalog(title, author,releaseDate,hardcover, object,isbn, numberOfCopies) VALUES (?, ?, ?, ?, ?, ?, ?)";
	private static final String READ_OBJECT_SQL_BY_TITLE = "SELECT object FROM bookCatalog WHERE title Like ?";
	private static final String READ_OBJECT_SQL_BY_AUTHOR = "SELECT object FROM bookCatalog WHERE author Like ?";
	private static final String READ_OBJECT_SQL_BY_RELEASE_DATE = "SELECT object FROM bookCatalog WHERE releaseDate = ?";
	private static final String READ_OBJECT_SQL_BY_ISBN = "SELECT object FROM bookCatalog WHERE ISBN = ?";
	private static final String ADD_MORE_COPIES_SQL = "UPDATE bookCatalog Set numberOfCopies = ? Where ISBN = ?";
	
	public Catalog(String libraryName) {
		Statement stmnt = null;
		String url = "jdbc:mysql://localhost/test?autoDeserialize=true";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			catalogConnection = DriverManager
					.getConnection(url, "root", "root");
			stmnt = catalogConnection.createStatement();
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}

		try {
			stmnt.execute("use " + libraryName);
		} catch (SQLException e) {
			try {
				stmnt.execute("create database " + libraryName);
				catalogConnection.close();
				@SuppressWarnings("unused")
				Catalog c = new Catalog(libraryName);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	public void add(Book b) throws SQLException {
		PreparedStatement pstmt = catalogConnection
				.prepareStatement(WRITE_OBJECT_SQL);
		pstmt.setString(1, b.getTitle());
		pstmt.setString(2, b.getAuthor());
		pstmt.setInt(3, b.getReleaseDate());
		pstmt.setInt(4, b.isHardcover() ? 0 : 1);
		pstmt.setObject(5, b);
		pstmt.setLong(6, b.getIsbn());
		pstmt.setInt(7, b.getNumberOfCopies());
		try{
			pstmt.executeUpdate();		
		}catch(SQLException e){
			Statement stmt = catalogConnection.createStatement();
			stmt.execute("create table bookCatalog(title varchar(255), author " +
					"varchar(255), releaseDate int(4), hardCover int(1), object " +
					"BLOB, ISBN BIGINT(13), numberOfCopies int(3))");
			stmt.close();
			add(b);
		}
		pstmt.close();
	}

	public ArrayList<Book> searchTitle(StringBuffer newTitle) {
		ArrayList<Book> results = new ArrayList<Book>();
		try {
			PreparedStatement pstmt = catalogConnection
					.prepareStatement(READ_OBJECT_SQL_BY_TITLE);
			newTitle.insert(0, "%");
			newTitle.append("%");
			pstmt.setString(1, newTitle.toString());
			try{
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					results.add((Book)rs.getObject("Object"));
				}
			    rs.close();
			}catch(SQLException e){
				Statement stmt = catalogConnection.createStatement();
				stmt.execute("create table bookCatalog(title varchar(255), author " +
						"varchar(255), releaseDate int(4), hardCover int(1), object " +
						"BLOB, ISBN BIGINT(13), numberOfCopies int(3))");
				stmt.close();
				newTitle.deleteCharAt(0);
				newTitle.deleteCharAt(newTitle.length()-1);
				searchTitle(newTitle);
			}
		    pstmt.close();
		    return results;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public ArrayList<Book> searchAuthor(StringBuffer authorName){
		ArrayList<Book> results = new ArrayList<Book>();
		try {
			PreparedStatement pstmt = catalogConnection
					.prepareStatement(READ_OBJECT_SQL_BY_AUTHOR);
			authorName.insert(0, "%");
			authorName.insert(authorName.length()-1, "%");
			System.out.println(authorName);
			pstmt.setString(1, authorName.toString());
			try{
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					results.add((Book)rs.getObject("Object"));
				}
			    rs.close();
			}catch(SQLException e){
				Statement stmt = catalogConnection.createStatement();
				stmt.execute("create table bookCatalog(title varchar(255), author " +
						"varchar(255), releaseDate int(4), hardCover int(1), object " +
						"BLOB, ISBN BIGINT(13), numberOfCopies int(3))");
				stmt.close();
				searchAuthor(authorName);
			}
		    pstmt.close();
		    return results;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}		
	}
	
	public ArrayList<Book> searchReleaseDate(int releaseDate){
		ArrayList<Book> results = new ArrayList<Book>();
		try {
			PreparedStatement pstmt = catalogConnection
					.prepareStatement(READ_OBJECT_SQL_BY_RELEASE_DATE);
			pstmt.setInt(1, releaseDate);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				results.add((Book)rs.getObject("Object"));
			}
		    rs.close();
		    pstmt.close();
		    return results;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}		
	}
	
	
	public ArrayList<Book> searchISBN(long isbn){
		ArrayList<Book> results = new ArrayList<Book>();
		try {
			PreparedStatement pstmt = catalogConnection
					.prepareStatement(READ_OBJECT_SQL_BY_ISBN);
			pstmt.setLong(1, isbn);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				results.add((Book)rs.getObject("Object"));
			}
		    rs.close();
		    pstmt.close();
		    return results;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}		
	}
	
	public void addNumberOfCopies(Book b, int newCopies){
		try {
			PreparedStatement pstmt = catalogConnection.prepareStatement(ADD_MORE_COPIES_SQL);
			pstmt.setInt(1, b.getNumberOfCopies() + newCopies);
			pstmt.setLong(2, b.getIsbn());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ArrayList<Book> searchTitle(String title) {
		StringBuffer newTitle = new StringBuffer(title);
		return searchTitle(newTitle);
	}

	public ArrayList<Book> searchAuthor(String text) {
		StringBuffer title = new StringBuffer(text);
		return searchAuthor(title);
	}
	

}
